#!/usr/bin/python
#coding: utf-8
from sqlalchemy import *
from sqlalchemy.orm import *
engine=create_engine("sqlite:///2011test.db",echo=True)
metadata=MetaData()

table_user=Table('user_table',metadata,
                 Column('id',Integer,primary_key=True),
                 Column('name',String),
                 Column('password',String),
                 Column('ntname',String))
class user(object):
    def __init__(self,name,password,ntname,id=None):
        if id:
            self.id=id
        self.name=name
        self.password=password
        self.ntname=ntname
        
    def __repr__(self):
        return "<user('id:%s,name:%s,passwd:%s,ntname:%s')>\n" %(self.id,self.name,self.password,self.ntname)




table_changping=Table('changping_t',metadata,
                      Column('id',Integer,primary_key=True),
                      Column('name',String),
                      Column('jiage',Integer),
                      Column('userID',Integer,ForeignKey('user_table.id')))

class changping(object):
    def __init__(self,name,jiage,userID=None,id=None):
        self.name=name
        self.jiage=jiage
        self.userID=userID
        if id:
            self.id=id
    def __repr__(self):
        return "<changping('%s,%s,%s')>\n" %(self.id,self.name,self.userID)
mapper(changping,table_changping)#,properties={'user':relation(user)}) 
mapper(user,table_user,properties={'changping': relationship(changping,backref='uer')})
#mapper(user,table_user,properties={'children': relation(changping)})


metadata.create_all(engine)

def test(id=None):
    s=Session(engine)
    if id:
        ins=user('ye','xiaoting','fire',id)
    else:
        ins=user('ye','xiaoting','fire')
    s.add(ins)
    s.commit()
    s.close()
def instchangping(id=None):
    s=Session(engine)
    if id:
        ins=changping('lenovo',5000,200,id)
    else:
        ins=changping('yexiaoting',510,200)
    s.add(ins)
    s.commit()
    s.close()
    
def query(obj):
    s=Session(engine)
    lists=s.query(obj).all()
    return lists

def onetomore(id):
    s=Session(engine)
    insertuser=user('yexiaoting','rootss','fire wolf',5556)
    insertuser.children.append(changping('DELL LENOVO',6000,4444))
    s.add(insertuser)
    s.commit()
    s.close()